with network_whole as (
    select code
         , name
         , virt_code
         , virt_name
    from jms_dim.dim_network_whole_massage
    where network_type = 6
),

     leader_config as (
         select agent_code
              , staff_code    --员工编码
              , staff_name    --员工名称
              , position_name --岗位名称
         from jms_dim.dim_tab_agent_leader_config_base
         group by agent_code
                , staff_code --员工编码
                , staff_name --员工名称
                , position_name --岗位名称

     ),
     net_agent_conf as (
         select network_code
              , proxy_code
              , proxy_name
         from jms_tmp.tmp_waybill_customer_complaint_0304
         group by network_code
                , proxy_code
                , proxy_name
     ),
     baocang_curr as (
         select t1.final_sign_network_code --最终签收派件网点编码
              , t1.final_sign_network_name --最终签收派件网点编码
              , t1.end_agent_code          --末端网点所属代理区编码
              , t1.end_agent_name          --末端网点所属代理区名称
              , t1.need_sign_count_1d      --1天应签收
              , t1.retention_count_1d      --1天内滞留
              , t1.need_sign_count_2d      --2天应签收
              , t1.retention_count_2d      --2天内滞留
              , t1.need_sign_count_3d      --3天应签收
              , t1.retention_count_3d      --3天内滞留
              , t1.need_sign_count_5d      --5天应签收
              , t1.retention_count_5d      --5天内滞留
              , t1.need_sign_count_7d      --7天应签收
              , t1.retention_count_7d      --7天内滞留
              , t1.scan_sum                --时效签收量
              , t1.yali_flag               --1代表压力值满足的要保留,0非代表不满足任何一种情况
              , t1.final_plan_sign_date    --规划签收日期
              , t1.end_provider_id         --末端网点所属省份id
              , t1.end_provider_name       --末端网点所属省份名称
              , t1.end_city_id             --末端网点所属城市id
              , t1.end_city_name           --末端网点所属城市名称
              , t1.district_code
              , t1.district_desc
              , t1.district_id
              , t1.area_code
              , t1.area_name
              , t1.1pressure               --1日内压力值
              , t1.3pressure               --3日内压力值
              , t1.5pressure               --5日内压力值
              , t1.7pressure               --7日内压力值
              , t1.1frame                  --1日内票量
              , t1.3frame                  --3日内票量
              , t1.5frame                  --5日内票量
              , t1.7frame                  --7日内票量
              , t1.one_pre_flag
              , t1.three_pre_flag
              , t1.yali_flag
         from jms_dm.dm_mng_explosion_warehouse_network_mid_dt t1
         where dt = '{{ execution_date | cst_ds }}'
           and yali_flag = 1
           and is_contain_difficult = 2
     ),
     ressutl_1 as (
         select t1.final_sign_network_code                           --最终签收派件网点编码
              , t1.final_sign_network_name                           --最终签收派件网点编码
              , t1.end_agent_code                                    --末端网点所属代理区编码
              , t1.end_agent_name                                    --末端网点所属代理区名称
              , t1.need_sign_count_1d                                --1天应签收
              , t1.retention_count_1d                                --1天内滞留
              , t1.need_sign_count_2d                                --2天应签收
              , t1.retention_count_2d                                --2天内滞留
              , t1.need_sign_count_3d                                --3天应签收
              , t1.retention_count_3d                                --3天内滞留
              , t1.need_sign_count_5d                                --5天应签收
              , t1.retention_count_5d                                --5天内滞留
              , t1.need_sign_count_7d                                --7天应签收
              , t1.retention_count_7d                                --7天内滞留
              , t1.scan_sum                                          --时效签收量
              , 1                                            as is_warn
              , t1.yali_flag
              , t1.final_plan_sign_date                              --规划签收日期
              , 1                                            as flag --1代表保留的,0代表要做剔除逻辑的
              , t3.staff_code                                        --员工编码
              , t3.staff_name                                        --员工名称
              , t3.position_name
              , t4.virt_code
              , t4.virt_name
              , if(t4.virt_code is not null, '虚拟代理区', '代理区') as agent_type
              , t1.end_provider_id                                   --末端网点所属省份id
              , t1.end_provider_name                                 --末端网点所属省份名称
              , t1.end_city_id                                       --末端网点所属城市id
              , t1.end_city_name                                     --末端网点所属城市名称
              , t1.district_code
              , t1.district_desc
              , t1.district_id
              , t1.area_code
              , t1.area_name
              , t1.1pressure                                         --1日内压力值
              , t1.3pressure                                         --3日内压力值
              , t1.5pressure                                         --5日内压力值
              , t1.7pressure                                         --7日内压力值
              , t1.1frame                                            --1日内票量
              , t1.3frame                                            --3日内票量
              , t1.5frame                                            --5日内票量
              , t1.7frame                                            --7日内票量
              , t1.one_pre_flag
              , t1.three_pre_flag
         from baocang_curr t1
                  left join leader_config t3
                            on t1.end_agent_code = t3.agent_code
                  left join network_whole t4
                            on t1.final_sign_network_code = t4.code
     )
insert
overwrite
table
jms_dm.dm_mng_explosion_warehouse_network_dt
partition
(
dt
)
select t1.final_sign_network_code
     , t1.final_sign_network_name
     , t1.end_agent_code
     , t1.end_agent_name
     , t1.need_sign_count_1d
     , t1.retention_count_1d
     , t1.need_sign_count_2d
     , t1.retention_count_2d
     , t1.need_sign_count_3d
     , t1.retention_count_3d
     , t1.need_sign_count_5d
     , t1.retention_count_5d
     , t1.need_sign_count_7d
     , t1.retention_count_7d
     , t1.scan_sum
     , t1.staff_code
     , t1.staff_name
     , t1.position_name
     , t1.is_warn
     , t1.final_plan_sign_date
     , t1.agent_type
     , t1.virt_code         --虚拟代理区code
     , t1.virt_name         --虚拟代理区name
     , t1.end_provider_id   --末端网点所属省份id
     , t1.end_provider_name --末端网点所属省份名称
     , t1.end_city_id       --末端网点所属城市id
     , t1.end_city_name     --末端网点所属城市名称
     , t1.district_code
     , t1.district_desc
     , t1.district_id
     , null                    as duration
     , t1.area_code
     , t1.area_name
     , t1.1pressure         --1日内压力值
     , t1.3pressure         --3日内压力值
     , t1.5pressure         --5日内压力值
     , t1.7pressure         --7日内压力值
     , t1.1frame            --1日内票量
     , t1.3frame            --3日内票量
     , t1.5frame            --5日内票量
     , t1.7frame            --7日内票量
     , t1.one_pre_flag
     , t1.three_pre_flag
     , t1.yali_flag
     , t1.final_plan_sign_date as dt
from ressutl_1 t1
    distribute by dt
;



insert
    overwrite
    table jms_dm.dm_mng_explosion_warehouse_network_dt
    partition
    (dt)
select t1.final_sign_network_code
     , t1.final_sign_network_name
     , t1.end_agent_code
     , t1.end_agent_name
     , t1.need_sign_count_1d
     , t1.retention_count_1d
     , t1.need_sign_count_2d
     , t1.retention_count_2d
     , t1.need_sign_count_3d
     , t1.retention_count_3d
     , t1.need_sign_count_5d
     , t1.retention_count_5d
     , t1.need_sign_count_7d
     , t1.retention_count_7d
     , t1.scan_sum
     , t1.staff_code
     , t1.staff_name
     , t1.position_name
     , t1.is_warn
     , t1.final_plan_sign_date
     , t1.agent_type
     , t1.virt_code         --虚拟代理区code
     , t1.virt_name         --虚拟代理区name
     , t1.end_provider_id   --末端网点所属省份id
     , t1.end_provider_name --末端网点所属省份名称
     , t1.end_city_id       --末端网点所属城市id
     , t1.end_city_name     --末端网点所属城市名称
     , t1.district_code
     , t1.district_desc
     , t1.district_id
     , t2.duration
     , t1.area_code
     , t1.area_name
     , t1.1pressure         --1日内压力值
     , t1.3pressure         --3日内压力值
     , t1.5pressure         --5日内压力值
     , t1.7pressure         --7日内压力值
     , t1.1frame            --1日内票量
     , t1.3frame            --3日内票量
     , t1.5frame            --5日内票量
     , t1.7frame            --7日内票量
     , t1.one_pre_flag
     , t1.three_pre_flag
     , t1.yali_flag
     , t1.dt
from (
         select *
         from jms_dm.dm_mng_explosion_warehouse_network_dt
         where dt = '{{ execution_date | cst_ds }}'
     ) t1
         left join
     (
         select final_sign_network_code
              , max(duration)     as duration
              , max(maxdate_time) as max_date_time
         from (
                  select final_sign_network_code
                       , date_sub(final_plan_sign_date, rn) as diff_date
                       , count(1)                           as duration
                       , max(final_plan_sign_date)          as maxdate_time
                  from (
                           select final_sign_network_code
                                , final_plan_sign_date
                                , row_number()
                                   over (partition by final_sign_network_code order by final_plan_sign_date asc) as rn
                           from jms_dm.dm_mng_explosion_warehouse_network_dt
                           where final_plan_sign_date >= date_add('{{ execution_date | cst_ds }}', -61)
                             and final_plan_sign_date <= '{{ execution_date | cst_ds }}'
                       ) t
                  group by final_sign_network_code, diff_date
              ) t
         where maxdate_time = '{{ execution_date | cst_ds }}'
         group by final_sign_network_code
     ) t2
     on t1.final_sign_network_code = t2.final_sign_network_code
    distribute by dt
;

